package com.laxcen.smoke.dao;

import com.laxcen.smoke.dto.res.OrderExtraRes;
import com.laxcen.smoke.dto.res.OrderRes;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

@Mapper
@Repository
public interface OrderDao {

    //    //添加操作订单详情表
    @Insert("insert into operaterecorddetailtable(id,recordid,smartno,boxno,optime,optype,createtime) values(#{id},#{recordid},#{smartno},#{boxno},#{optime},#{optype},#{createtime}) ")
    int insertuporderdetail(String id, String recordid, String smartno, String boxno, Date optime, int optype, Date createtime);

    //添加操作订单表
    @Insert("insert into operaterecord(id,smartno,boxno,opid,optime,optype,orderno,createtime) values(#{id},#{smartno},#{boxno},#{opid},#{optime},#{optype},#{orderno},#{createtime}) ")
    int insertuporder(String id, String smartno, String boxno, String opid, Date optime, int optype, String orderno, Date createtime);

    //获取订单详情(optype 0存入 1取出)
    @Select("select id,smartno,boxno,optype,opid,optime,orderno from operaterecord  where orderno = #{orderno} and optype = #{optype} order by optime desc")
    List<OrderRes> getOrderDetailByOpType(String orderno, int optype);

    @Select("select * from operaterecord where id = #{id}")
    OrderRes getOrderResById(String id);

    @Select("select id,smartno,GROUP_CONCAT(boxno separator',') as boxno,GROUP_CONCAT(optype separator',') as optype,opid,optime,orderno from operaterecord where  !FIND_IN_SET('2',optype) and !FIND_IN_SET('3',optype) group by orderno order by optime desc")
    List<OrderRes> getOrderList();

    @Select("select op.id,op.smartno,GROUP_CONCAT(op.boxno separator',') as boxno,GROUP_CONCAT(op.optype  separator',') as optype,op.opid,optime,op.orderno from operaterecord  op left join smart sm on op.smartno = sm.no where  sm.no in (select distinct sm.no from smart sm right join real_smart_smartboard rss on sm.id = rss.SmartId where sm.address like CONCAT('%',#{place},'%')) and !FIND_IN_SET('2',optype) and !FIND_IN_SET('3',optype) group by op.orderno order by op.optime desc")
    List<OrderRes> getOrderListRole(String place);

    @Select("select op.id,op.smartno,GROUP_CONCAT(op.boxno separator',') as boxno,GROUP_CONCAT(op.optype  separator',') as optype,op.opid,optime,op.orderno from operaterecord  op left join smart sm on op.smartno = sm.no where sm.no in (select distinct sm.no from smart sm right join real_smart_smartboard rss on sm.id = rss.SmartId where orderno like CONCAT('%',#{searchtext},'%') or smartno like CONCAT('%',#{searchtext},'%') or opid like CONCAT('%',#{searchtext},'%')  ) and !FIND_IN_SET('2',optype) and !FIND_IN_SET('3',optype) group by op.orderno order by op.optime desc")
    List<OrderRes> getOrderListBySearch(String searchtext);

    @Select("select op.id,op.smartno,GROUP_CONCAT(op.boxno separator',') as boxno,GROUP_CONCAT(op.optype  separator',') as optype,op.opid,optime,op.orderno from operaterecord  op left join smart sm on op.smartno = sm.no where sm.no in (select distinct sm.no from smart sm right join real_smart_smartboard rss on sm.id = rss.SmartId where sm.address like CONCAT('%',#{place},'%')  and orderno like CONCAT('%',#{searchtext},'%') or smartno like CONCAT('%',#{searchtext},'%') or opid like CONCAT('%',#{searchtext},'%')  ) and !FIND_IN_SET('2',optype) and !FIND_IN_SET('3',optype) group by op.orderno order by op.optime desc")
    List<OrderRes> getOrderListBySearchRole(String searchtext, String place);

    @Select("select id,smartno,GROUP_CONCAT(boxno separator',') as boxno,GROUP_CONCAT(optype separator',') as optype,opid,optime,orderno from operaterecord where optime between #{starttime} and #{endtime}  and !FIND_IN_SET('2',optype) and !FIND_IN_SET('3',optype) group by orderno order by optime desc")
    List<OrderRes> getOrderListByTime(Date starttime, Date endtime);

    @Select("select op.id,op.smartno,GROUP_CONCAT(op.boxno separator',') as boxno,GROUP_CONCAT(op.optype  separator',') as optype,op.opid,optime,op.orderno from operaterecord  op left join smart sm on op.smartno = sm.no where op.optime between #{starttime} and #{endtime}   and sm.no in (select distinct sm.no from smart sm right join real_smart_smartboard rss on sm.id = rss.SmartId where sm.address like CONCAT('%',#{place},'%')) and !FIND_IN_SET('2',optype) and !FIND_IN_SET('3',optype) group by op.orderno order by op.optime desc")
    List<OrderRes> getOrderListByTimeRole(Date starttime, Date endtime, String place);

    @Select("select id,smartno,GROUP_CONCAT(boxno separator',') as boxno,GROUP_CONCAT(optype separator',') as optype,opid,optime,orderno from operaterecord where optime between #{starttime} and #{endtime} and !FIND_IN_SET('2',optype) and !FIND_IN_SET('3',optype) and orderno like CONCAT('%',#{searchtext},'%') or smartno like CONCAT('%',#{searchtext},'%') or opid like CONCAT('%',#{searchtext},'%') group by orderno order by optime desc")
    List<OrderRes> getOrderListByAll(String searchtext, Date starttime, Date endtime);

    @Select("select op.id,op.smartno,GROUP_CONCAT(op.boxno separator',') as boxno,GROUP_CONCAT(op.optype  separator',') as optype,op.opid,optime,op.orderno from operaterecord  op left join smart sm on op.smartno = sm.no where  op.optime between #{starttime} and #{endtime}  and sm.no in (select distinct sm.no from smart sm right join real_smart_smartboard rss on sm.id = rss.SmartId where sm.address like CONCAT('%',#{place},'%') and op.orderno like CONCAT('%',#{searchtext},'%') or op.smartno like CONCAT('%',#{searchtext},'%') or op.opid like CONCAT('%',#{searchtext},'%'))  and !FIND_IN_SET('2',optype) and !FIND_IN_SET('3',optype) group by op.orderno order by op.optime desc")
    List<OrderRes> getOrderListByAllRole(String searchtext, Date starttime, Date endtime, String place);

//    @Select("select id,smartno,boxno,opid,opname,optime,optype,orderno from operaterecord where id = #{id}")
//    OrderRes getOneOrder(String id);

    @Select("select id from operaterecord where orderno = #{orderno}")
    List<String> getIdByOrderno(String orderno);

    @Select("select id,smartno,boxno,optype,opresult,opouttime,remark from operaterecorddetailtable where recordid = #{recordid}")
    OrderExtraRes getOrderDetailList(String recordid);

    @Select("select distinct sbox.no boxno from real_board_box rbb left join smartbox sbox on rbb.BoxId = sbox.id where rbb.no = #{boxno}")
    String getBoxno(String boxno);

    @Select("select distinct sm.no smartno from real_smart_smartboard rss left join smart sm on rss.smartid = sm.id where sm.no = #{smartno} ")
    String getSmartsno(String smartno);

    @Update("update operaterecorddetailtable set opresult = #{opresult},remark = #{remark},opouttime = #{opouttime} where id = #{id}")
    int updated(String id, int opresult, String remark, Date opouttime);

}
